<?php
if (!defined('BASEPATH'))
	exit('No direct script access allowed');

class land_manage extends CI_Controller {
	var $data = array();
	var $ServerPath = "/ci_ube";
	public function __construct() {
		parent::__construct();
		$this -> output -> cache(0);
		$this -> data['nowmenu'] = "land";
		$this -> data['title'] = "Static Content Management";
		$this -> data['current_module'] = site_url("land_manage");
		$this -> data['role_id'] = $this -> session -> userdata('role_id');
	}

	public function index() {
		$this -> load -> view('land2/land_dashboard', $this -> data);
	}

	public function landmanager() {
		$this -> load -> view('land2/land_add_land', $this -> data);
	}

	public function getland_data($page = 0, $limit = 0) {

		$query = $this -> db -> query("SELECT
    `land`.`Updatetime`
    , `land`.`landid`
    , `landtype`.`name` AS  `landtype`
    , `land_document_type`.`land_document_type_name`
    , `vendor`.`vfirstname`
    , `vendor`.`vlastname`
FROM

ube.land left join ube.land_address 
on ube.land_address.land_addressid = ube.land.land_addressid

left join ube.land_doc on ube.land_doc.land_docid =  ube.land_address.land_docid

left join ube.landtype on 
ube.landtype.landtypeid=ube.land.landtypeid

left join ube.land_document_type on  ube.land_document_type.land_document_type_id=ube.land.land_document_type_id

left join ube.vendor on ube.vendor.uuid=ube.land.verdor_id order by Updatetime desc");

		$resulthtml = '<table  class="listing" cellpadding="0" cellspacing="0">
                        <tr>
                          <th width="100">วัน/เวลา</th>
                          <th width="105">เลขที่โฉนด</th>
                          <th width="117">ประเภทเอกสารสิทธิ</th>
                          <th width="189">ชื่อเจ้าของที่ดิน</th>
                          <th width="103">ประเภท</th>
                          <th width="51">edit</th>
                          <th width="63">delete</th>
                        </tr>';
		if ($query -> num_rows() > 0) {
			$count = 0;
			foreach ($query->result() as $row) {
				$resulthtml .= "<tr>";
				$resulthtml .= "<td class=\"style1\">" . $row -> Updatetime . "</td>";
				$resulthtml .= "<td class=\"style1\">" . "HS" . sprintf("%06d", $row -> landid) . "</td>";
				$resulthtml .= "<td>" . $row -> land_document_type_name . "</td>";
				$resulthtml .= "<td>" . $row -> vfirstname . "  " . $row -> vlastname . "</td>";
				$resulthtml .= "<td>" . $row -> landtype . "</td>";
				$resulthtml .= "<td><center><a id='editcontent" . $count . "' href='" . site_url("land_manage") . "/editland/" . $row -> landid . "'><img src='" . base_url() . "images/" . "icon_edit.png'/></a></center></td>";
				$resulthtml .= "<td><center><a href=\"javascript:delete('" . $row -> landid . "')\">" . "<img src='" . base_url() . "images/" . "icon_delete.png'/></a></center></td>";
				$resulthtml .= "<tr>" . '<script> $("#editcontent' . $count . '").fancybox({"autoScale":false,"onClosed": function() {  location.reload(true);} });</script>';
				$count = $count + 1;
			}
		} else {
			$resulthtml .= "<tr><td colspan='7'>No data</td></tr>";
		}
		echo $resulthtml . "</table>";
	}

	public function getaddressdetail() {
		$query = $this -> db -> query("SELECT
			    `land`.`Updatetime`
			    , `land`.`landid`
			    , `landtype`.`name` AS `landtype`
			    , `land_document_type`.`land_document_type_name`
			    , `vendor`.`vfirstname`
			    , `vendor`.`vlastname`
			    , `land_address`.`housenum`
			    , `land_address`.`village`
			    , `land_address`.`moono`
			    , `land_address`.`street`
			    , `land_address`.`district`
			    , `land_address`.`city`
			    , `land_address`.`province`
			    , `land_address`.`country`
			    , `land_doc`.`land_docid`
			    , `land_doc`.`land_file`,  `land_document_type`.`land_document_type_id`
    , `landtype`.`landtypeid`
			FROM
			 ube.land left join ube.land_address 
on ube.land_address.land_addressid = ube.land.land_addressid

left join ube.land_doc on ube.land_doc.land_docid =  ube.land_address.land_docid

left join ube.landtype on 
ube.landtype.landtypeid=ube.land.landtypeid

left join ube.land_document_type on  ube.land_document_type.land_document_type_id=ube.land.land_document_type_id

left join ube.vendor on ube.vendor.uuid=ube.land.verdor_id  where landid=" . $_REQUEST['landid']);
		$tmp = json_encode($query -> result_array());
		$tmp = str_replace("[", "", $tmp);
		$tmp = str_replace("]", "", $tmp);
		echo $tmp;
	}

	public function getxxx() {

	}

	public function editland($id) {

		$query = $this -> db -> query("SELECT * FROM  `land_document_type`");
		$documentTypr = array();
		$documentTypr[] = "Select";
		foreach ($query->result() as $row) {
			$documentTypr[$row -> land_document_type_id] = $row -> land_document_type_name;
		}
		$this -> data['documenttype'] = $documentTypr;

		$query = $this -> db -> query("SELECT * FROM  `landtype`");
		$documentTypr = array();
		$documentTypr[] = "Select";
		foreach ($query->result() as $row) {
			$documentTypr[$row -> landtypeid] = $row -> name;
		}
		$this -> data['landid'] = $id;
		$this -> data['landtype'] = $documentTypr;
		$this -> data['flagedit'] = true;
		$this -> load -> view('land2/land_upload', $this -> data);
	}

	public function deleteland() {

		$this -> db -> where('landid', $_REQUEST['landid']);
		$this -> db -> update('land', array('FLAG_ACTIVE' => "1"));
	}

	public function popup_uploadland() {
		$query = $this -> db -> query("SELECT * FROM  `land_document_type`");
		$documentTypr = array();
		$documentTypr[] = "Select";
		foreach ($query->result() as $row) {
			$documentTypr[$row -> land_document_type_id] = $row -> land_document_type_name;
		}
		$this -> data['documenttype'] = $documentTypr;

		$query = $this -> db -> query("SELECT * FROM  `landtype`");
		$documentTypr = array();
		$documentTypr[] = "Select";
		foreach ($query->result() as $row) {
			$documentTypr[$row -> landtypeid] = $row -> name;
		}
		$this -> data['landtype'] = $documentTypr;

		$this -> load -> view('land2/land_upload', $this -> data);

	}

	public function geography() {
		$query = $this -> db -> query("SELECT * FROM  `m_geography`");
		$tmp = json_encode($query -> result_array());
		// $tmp = str_replace("[", "", $tmp);
		// $tmp = str_replace("]", "", $tmp);
		echo $tmp;

	}

	public function province() {
		$query = $this -> db -> query("SELECT * FROM  `m_province` where GEO_ID=" . $_REQUEST['geoid']);
		$tmp = json_encode($query -> result_array());
		echo $tmp;
	}

	public function provinceall() {
		$query = $this -> db -> query("SELECT * FROM  `m_province` ");
		$tmp = json_encode($query -> result_array());
		echo $tmp;
	}

	public function amphur() {
		$query = $this -> db -> query("SELECT * FROM  `m_amphur` where PROVINCE_ID=" . $_REQUEST['provinceid']);
		$tmp = json_encode($query -> result_array());
		echo $tmp;
	}

	public function district() {
		$query = $this -> db -> query("SELECT * FROM  `m_district` where AMPHUR_ID=" . $_REQUEST['amphurid']);
		$tmp = json_encode($query -> result_array());
		echo $tmp;
	}

	public function save_address() {
		$recordLand = array();
		if ($_REQUEST['land_document_type_id'] != "" && $_REQUEST['land_document_type_id'] != "0") {
			$recordLand['land_document_type_id'] = $_REQUEST['land_document_type_id'];
		}
		if ($_REQUEST['land_document_type_id'] != "" && $_REQUEST['land_document_type_id'] != "0") {
			$recordLand['landtypeid'] = $_REQUEST['landtypeid'];
		}
		if ($_REQUEST['uuid'] != "" && $_REQUEST['uuid'] != "0") {
			$recordLand['verdor_id'] = $_REQUEST['uuid'];
		}

		$this -> db -> where('landid', $_REQUEST['landid']);
		$this -> db -> update('land', $recordLand);

		$query2 = $this -> db -> query('SELECT land_addressid FROM land where landid=' . $_REQUEST['landid']);
		$row = $query2 -> row();

		$record = array();
		$record['housenum'] = $_REQUEST['housenum'];
		$record['street'] = $_REQUEST['street'];
		$record['country'] = $_REQUEST['country'];
		$record['province'] = $_REQUEST['province'];
		$record['city'] = $_REQUEST['city'];
		$record['district'] = $_REQUEST['district'];
		$record['moono'] = $_REQUEST['moono'];

		$this -> db -> where('land_addressid', $row -> land_addressid);
		$this -> db -> update('land_address', $record);
		//$this -> db -> update("land_address", $record, "land_addressid=" . $row -> land_addressid);
		echo "Complete";

	}

	public function map($landid) {
		$query2 = $this -> db -> query('SELECT Lat,Lng FROM land where landid=' . $landid);

		$row = $query2 -> row();
		//14.028, 100.657
		$colorPolygon = "";
		if ($query2 -> num_rows() > 0) {

			$this -> data['current_lat'] = $row -> Lat;
			$this -> data['current_lng'] = $row -> Lng;
		} else {
			$colorPolygon = "00005A";
			$this -> data['current_lat'] = "14.028";
			$this -> data['current_lng'] = "100.657";
		}
		$count = 0;
		$javaScipt = "";
		$START_RED = 0;
		$START_GREEN = 0;
		$START_BLUE = 90;
		$queryPolygon = $this -> db -> query("SELECT * FROM land_polygon_map where LAND_ID=" . $landid);
		$tmpString = "var paths" . $count . " =[";
		foreach ($queryPolygon->result() as $rowPolygon) {
			$tmpString .= "new google.maps.LatLng(" . $rowPolygon -> Lat . "," . $rowPolygon -> Log . "),";
		}
		$queryPolygon -> free_result();
		$tmpString .= "];";

		$hexColor = "#ff0000";
		$tmpString .= "var shape" . $count . " = new google.maps.Polygon({
					paths :paths" . $count . ",
					strokeColor : '" . $hexColor . "',
					strokeOpacity : 0.8,
					strokeWeight : 2,
					fillColor : '" . $hexColor . "',
					fillOpacity : 0.35
				});
				 google.maps.event.addListener(shape" . $count . ", 'click', function() {
				 	
	
				});
				shape" . $count . ".setMap(map);";

		$javaScipt .= $tmpString;
		$this -> data['map_script'] = $javaScipt;
		$this -> load -> view('land/map', $this -> data);
	}

	public function uploadlanddoc() {
		// $this -> db -> insert('land_doc', array('land_file' => $save_tmp));
		// $landDocID = $this -> db -> insert_id();
		if (isset($_FILES["document_filexxx"]["name"]) && $_FILES["document_filexxx"]["name"] != "") {
			$save_tmp = time() . $_FILES["document_filexxx"]["name"];
			$saveKMLPath = $_SERVER['DOCUMENT_ROOT'] . "/ci_ube/data/doc/" . $save_tmp;
			if (move_uploaded_file($_FILES["document_filexxx"]["tmp_name"], $saveKMLPath)) {
				$this -> db -> insert('land_doc', array('land_file' => $save_tmp));
				$landDocID = $this -> db -> insert_id();
				$this -> db -> update("land_address", array("land_docid" => $landDocID));
				echo $save_tmp;
			}

		}
	}

	public function getvendorlst() {
		$query = $this -> db -> query('SELECT uuid,vfirstname,vlastname FROM vendor where vfirstname like ' . "'%" . $_REQUEST['queryString'] . "%'");
		foreach ($query->result() as $row) {
			echo "<li onClick=\"fill(" . $row -> uuid . ",'" . $row -> vfirstname . "  " . $row -> vlastname . "');\">" . $row -> vfirstname . "  " . $row -> vlastname . '</li>';
		}
	}

	public function uploadgpx() {

		if (isset($_FILES["uploadfile"]["name"]) && $_FILES["uploadfile"]["name"] != "") {
			$save_tmp = time() . $_FILES["uploadfile"]["name"];
			if (substr($_FILES["uploadfile"]["name"], -4) == ".kml") {

				$saveKMLPath = $_SERVER['DOCUMENT_ROOT'] . "/ci_ube/data/kml/" . $save_tmp;
				if (move_uploaded_file($_FILES["uploadfile"]["tmp_name"], $saveKMLPath)) {

					// $this -> db -> insert('land_doc', array('land_file' => $save_tmp));
					// $landDocID = $this -> db -> insert_id();

					$xml = simplexml_load_file($saveKMLPath);
					$count = 0;
					$LandID = "";
					foreach ($xml->Document->Placemark as $place) {

						$checkHaveInDB = $this -> db -> query("SELECT landid FROM land where name='" . $place -> name . "'");
						$lastInsertID = "";

						$AddressID = "";
						$count = $count + 1;
						$LandName_tm = "";
						$datatmp = array();
						if ($checkHaveInDB -> num_rows() > 0) {
							$row = $checkHaveInDB -> row();
							$LandID = $row -> landid;
							$this -> db -> query("delete from  land_polygon_map where LAND_ID=" . $LandID);
						} else {
							$datatmp = array('name' => "" . $place -> name);
							$LandName_tm = $place -> name;
							foreach ($place->LookAt as $lookAtArea) {
								$datatmp['Lat'] = $lookAtArea -> latitude;
								$datatmp['Lng'] = $lookAtArea -> longitude;

							}

						}
						// $dataLandAddress = array('land_docid' => $landDocID);
						$this -> db -> insert('land_address', array('housenum' => ""));
						$datatmp['land_addressid'] = $this -> db -> insert_id();

						//$datatmp['Updatetime']= date("d-m-o")." ".date('H:i:s');
						$this -> db -> insert('land', $datatmp);
						$LandID = $this -> db -> insert_id();
						$tmpPoligon = "";
						$latTmp = "";
						$logtmp = "";
						foreach ($place->Polygon as $areaPolygon) {
							$tmp1 = trim($areaPolygon -> outerBoundaryIs[0] -> LinearRing -> coordinates);
							$tmp1 = str_replace(",0", "#", $tmp1);
							$tmp1 = str_replace(",", " ", $tmp1);
							$tmp1 = str_replace("#", ",", $tmp1);
							$tmp1 = substr($tmp1, 0, strlen($tmp1) - 1);
							$tmp1 = explode(",", $tmp1);
							$tmpPolygon = "";
							for ($i = 0; $i < count($tmp1); $i++) {
								$tmpArray = array();
								$tmpArray = explode(" ", trim($tmp1[$i]));
								for ($j = 0; $j < count($tmpArray); $j = $j + 2) {
									$LatLongTmp = array();
									$LatLongTmp['Log'] = $tmpArray[$j];
									$LatLongTmp['Lat'] = $tmpArray[$j + 1];
									$LatLongTmp['LAND_ID'] = $LandID;
									$this -> db -> insert('land_polygon_map', $LatLongTmp);

									$tmpPolygon = $tmpPolygon . $tmpArray[$j + 1] . " " . $tmpArray[$j] . ",";
								}
								$logtmp = $tmpArray[0];
								$latTmp = $tmpArray[1];
							}
							$tmpPolygon = substr($tmpPolygon, 0, strlen($tmpPolygon) - 1);
							$sqlInsertPoligon = "INSERT INTO land_polygon(`name`,`polygon`) VALUES ('" . $LandName_tm . "', GeomFromText('POLYGON(( " . $tmpPolygon . "))'))";
							$this -> db -> query($sqlInsertPoligon);
							$polygonID = $this -> db -> insert_id();
							$calculateArea = $this -> db -> query("SELECT area(polygon) as areasize FROM  land_polygon where land_polygonid=" . $polygonID);
							$rowResultArea = $calculateArea -> row();

							$areaSize = (($rowResultArea -> areasize / 1000000) / 0.0016);
							//	$this->db->trans_complete();
							$this -> db -> query("update land set land_polygonid=" . $polygonID . ",Lat=" . $latTmp . ",Lng=" . $logtmp . " where landid=" . $LandID);
							//$this -> db -> query("update land set land_polygonid=" . $polygonID . " where landid=" . $LandID);
							$this -> db -> query("update land_polygon set Size=" . $areaSize . " where land_polygonid=" . $polygonID);
							// $this -> db -> update('land',  array('land_polygonid' => $polygonID),"landid=".$LandID);

							//$this -> db -> update('land_polygon', array('Size' => $rowResultArea -> areasize), "land_polygonid=" . $polygonID);
						}

					}
					echo $LandID;
				} else {

				}
			} else {
				$saveKMLPath = $_SERVER['DOCUMENT_ROOT'] . "/ci_ube/data/gpx/" . $save_tmp;
				if (move_uploaded_file($_FILES["uploadfile"]["tmp_name"], $saveKMLPath)) {

					// $this -> db -> insert('land_doc', array('land_file' => $save_tmp));
					// $landDocID = $this -> db -> insert_id();

					$xml = simplexml_load_file($saveKMLPath);
					$count = 0;
					$LandID = "";
					foreach ($xml -> trk as $place) {

						$checkHaveInDB = $this -> db -> query("SELECT landid,land_polygonid FROM land where name='" . $place -> name . "'");
						$lastInsertID = "";

						$AddressID = "";
						$count = $count + 1;
						$LandName_tm = "";
						$dataLand = array();
						if ($checkHaveInDB -> num_rows() > 0) {
							$row = $checkHaveInDB -> row();
							$LandID = $row -> landid;
							$this -> db -> delete('land_polygon_map', array('LAND_ID' => $LandID));
						} else {

							$dataLand['name'] = "" . $place -> name;
							$LandName_tm = $place -> name;
							if (count($place -> trkseg -> trkpt) > 0) {
								$attrs = $place -> trkseg[0] -> trkpt -> attributes();
								$dataLand['Lat'] = $attrs['lat'];
								$dataLand['Lng'] = $attrs['lon'];
							}
						}

						// $dataLandAddress = array('land_docid' => $landDocID);
						$this -> db -> insert('land_address', array('housenum' => ""));

						$dataLand['land_addressid'] = $this -> db -> insert_id();
						//$dataLand['Updatetime']= date("d-m-o")." ".date('H:i:s');
						$this -> db -> insert('land', $dataLand);
						$LandID = $this -> db -> insert_id();

						$tmpPolygonX = "";
						$latTmp = "";
						$logtmp = "";
						foreach ($place -> trkseg->trkpt as $locationRow) {
							$attrs = $locationRow -> attributes();
							$LatLongTmp = array();
							$LatLongTmp['Lat'] = $attrs['lat'];
							$LatLongTmp['Log'] = $attrs['lon'];
							$latTmp = $attrs['lat'];
							$logtmp = $attrs['lon'];
							$LatLongTmp['LAND_ID'] = $LandID;
							$this -> db -> insert('land_polygon_map', $LatLongTmp);

						}
						$sqlInsertPoligon = "INSERT INTO land_polygon(`name`) VALUES ('" . $LandName_tm . "')";
						$this -> db -> query($sqlInsertPoligon);
						$polygonID = $this -> db -> insert_id();
						
						//	$this->db->trans_complete();
						$this -> db -> query("update land set land_polygonid=" . $polygonID . ",Lat=" . $latTmp . ",Lng=" . $logtmp . " where landid=" . $LandID);
					//	$this -> db -> query("update land set Lat=" . $latTmp . ",Lng=" . $logtmp . " where landid=" . $LandID);

					}
					echo $LandID;
				}
				//	echo $count;
			}
		}
	}

}
